from openpyxl import load_workbook
from openpyxl.workbook import Workbook
import datetime
import warnings
import os
import datetime


today = datetime.date.today()
today = str(today)
print(today)

today_lst = today.split('-')
today_words = str(today_lst[1]) +"月" + str(today_lst[2]) +"日"
print(today_words)

find_file_str = "质差+贬损用户详情统计" + today

def find_file_by_substring(search_dir, substring):
    """
    在指定目录及子目录中查找包含特定字符串的文件
    :param search_dir: 要搜索的目录路径
    :param substring: 文件名包含的字符串片段
    :return: 包含完整路径的匹配文件列表
    """
    matches = []
    # 遍历目录树
    for root, dirs, files in os.walk(search_dir):
        for file in files:
            if substring in file:  # 检查文件名是否包含目标字符串
                full_path = os.path.join(root, file)
                matches.append(full_path)
    return matches



warnings.filterwarnings('ignore', category=UserWarning, message='Workbook contains no default style')

def safe_divide(a, b):
    try:
        return a / b
    except ZeroDivisionError:
        return 0  # 返回字符串错误标识

def calculate_rank(arr):
    # 生成带索引的元组列表，并按值降序、索引升序排序
    sorted_with_indices = sorted(enumerate(arr), key=lambda x: (-x[1], x[0]))
    ranks = [0] * len(arr)
    prev_value = None
    prev_rank = 0

    for i, (original_idx, value) in enumerate(sorted_with_indices):
        if value != prev_value:
            current_rank = i + 1  # 排名从1开始
            prev_rank = current_rank
            prev_value = value
        ranks[original_idx] = prev_rank  # 记录原始索引对应的排名

    return ranks

def Company_name(str):
    company_name = ""
    if "丹江口" in str:
        company_name = "丹江口市分公司"
    if "房县" in str:
        company_name = "房县分公司"
    if "郧西" in str:
        company_name = "郧西县分公司"
    if "郧阳" in str:
        company_name = "郧阳区分公司"
    if "竹山" in str:
        company_name = "竹山县分公司"
    if "竹溪" in str:
        company_name = "竹溪县分公司"
    if "五堰" in str:
        company_name = "五堰营维中心"
    if "茅箭" in str:
        company_name = "茅箭营维中心"
    if "张湾" in str:
        company_name = "张湾营维中心"
    if "武当山" in str:
        company_name = "武当山营维中心"
    if "十堰现场综合化维护室" in str:
        company_name = "线路中心"


    return company_name

def shifouluyin(str):
    if str == "0":
        return "否"
    if str == "1":
        return "是"

def zhichaqiansanyuhousan(lst):
    lst_zongqiansan = []
    lst_zonghousan = []
    for target in [1, 2, 3]:
        lst_zongqiansan.extend([i for i, x in enumerate(lst) if x == target])
    for target in [10, 9, 8]:
        lst_zonghousan.extend([i for i, x in enumerate(lst) if x == target])
    lst_zong = lst_zongqiansan + lst_zonghousan
    lst_zong_name = []
    for i in lst_zong:
        if i == 0:
            lst_zong_name.append("丹江")
        if i == 1:
            lst_zong_name.append("房县")
        if i == 2:
            lst_zong_name.append("郧西")
        if i == 3:
            lst_zong_name.append("郧阳")
        if i == 4:
            lst_zong_name.append("竹山")
        if i == 5:
            lst_zong_name.append("竹溪")
        if i == 6:
            lst_zong_name.append("五堰")
        if i == 7:
            lst_zong_name.append("茅箭")
        if i == 8:
            lst_zong_name.append("张湾")
        if i == 9:
            lst_zong_name.append("武当山")
    return lst_zong_name


# 定义文件路径：（刘工路径：C:/Users/Administer/Desktop/）
# real_file_name = find_file_by_substring("C:/Users/Administer/Desktop",find_file_str)
# print("文件名称：")
# scr_sheet_file_path = real_file_name[0]
# des_sheet_file_path = "C:/Users/LYP/Desktop/8月质差工单完成情况.xlsx"

# 定义文件路径：（云电脑路径：D:/desktop）
# scr_sheet_file_path = "D:/desktop/质差+贬损用户详情统计.xlsx"
# des_sheet_file_path = "D:/desktop/8月质差工单完成情况.xlsx"


real_file_name = find_file_by_substring("D:/desktop/auto/out/excel/",find_file_str)
real_file_name = "D:/desktop/auto/excel/质差+贬损用户详情统计2025-10-10-09-04-16.xlsx"
print("文件名称：", real_file_name)
scr_sheet_file_path = real_file_name
des_sheet_file_path = "D:/desktop/auto/out/excel/10月质差工单完成情况.xlsx"


print("读入表格...")

src_wb = load_workbook(scr_sheet_file_path)
des_wb = load_workbook(des_sheet_file_path)

src_sheet_name = "sheet1"
src_sheet = src_wb[src_sheet_name]

des_sheet1_name = "9月派单完成情况"
des_sheet2_name = "1.满意度+质差清单"
des_sheet3_name = "2.网关承载不足清单"
des_sheet4_name = "分局修复率"

des_sheet1 = des_wb[des_sheet1_name]
des_sheet2 = des_wb[des_sheet2_name]
des_sheet3 = des_wb[des_sheet3_name]
des_sheet4 = des_wb[des_sheet4_name]

paidan_num = [0,0,0,0,0,0,0,0,0,0,0,0]        # 索引0表示丹江，1表示房县，以此类推，最后一个为总的
zaituweichuli_num = [0,0,0,0,0,0,0,0,0,0,0,0]     # 在途，未处理

biansunyonghu_num = [0,0,0,0,0,0,0,0,0,0,0,0]     # 贬损用户数
biansunyonghu_yixiufu_num = [0,0,0,0,0,0,0,0,0,0,0,0]  # 贬损用户已修复数

zhicha_num = [0,0,0,0,0,0,0,0,0,0,0,0]        # 质差数
zhicha_yixiufu_num = [0,0,0,0,0,0,0,0,0,0,0,0]    # 质差已修复数

wangguanchengzaibuzu_num = [0,0,0,0,0,0,0,0,0,0,0,0]
wangguanchengzaibuzu_yixiufu_num = [0,0,0,0,0,0,0,0,0,0,0,0]



def statistic(area="",idx = 0):
    for row in src_sheet.iter_rows(min_row=2):
        if area == "十堰控单策略":
            if row[39].value=="杨宜辉":
                idx = 6
            if row[39].value=="刘国志" or row[39].value == "高思成":
                idx = 7
            if row[39].value=="张勇":
                idx = 8

        if area in row[48].value:           # 处理派单总数
            paidan_num[idx] += 1
            paidan_num[11] += 1
            if row[38].value == "":
                zaituweichuli_num[idx] += 1
                zaituweichuli_num[11] += 1

            if row[24].value == "贬损用户修复派单":
                biansunyonghu_num[idx] += 1
                biansunyonghu_num[11] += 1
                if row[2].value == "已修复":
                    biansunyonghu_yixiufu_num[idx] += 1
                    biansunyonghu_yixiufu_num[11] += 1

            if row[24].value != "贬损用户修复派单":
                zhicha_num[idx] += 1
                zhicha_num[11] += 1
                if row[2].value == "已修复":
                    zhicha_yixiufu_num[idx] += 1
                    zhicha_yixiufu_num[11] += 1

            if row[24].value == "网关承载不足派单":
                wangguanchengzaibuzu_num[idx] += 1
                wangguanchengzaibuzu_num[11] += 1
                if row[2].value == "已修复":
                    wangguanchengzaibuzu_yixiufu_num[idx] += 1
                    wangguanchengzaibuzu_yixiufu_num[11] += 1

    print(area+"数据处理完毕")


statistic("丹江口",0)
statistic("房县",1)
statistic("郧西",2)
statistic("郧阳",3)
statistic("竹山",4)
statistic("竹溪",5)
statistic("五堰",6)
statistic("茅箭",7)
statistic("张湾",8)
statistic("武当山",9)
statistic("十堰现场综合化维护室",10)
statistic("十堰控单策略")

print()
print('--------------------------------数据情况-----------------------------------------')
print("感知类工单派单总量：",paidan_num)
print("在途未处理工单量：",zaituweichuli_num)
# print(biansunyonghu_num)
# print(biansunyonghu_yixiufu_num)
# print(zhicha_num)
# print(zhicha_yixiufu_num)

yixiufu_num = [x + y for x, y in zip(biansunyonghu_yixiufu_num, zhicha_yixiufu_num)]

manyidu_rate = [safe_divide(x,y) for x, y in zip(biansunyonghu_yixiufu_num,biansunyonghu_num )]
zhicha_rate = [safe_divide(x,y) for x, y in zip(zhicha_yixiufu_num,zhicha_num )]
ganzhilei_rate = [safe_divide(x,y) for x, y in zip(yixiufu_num,paidan_num )]

wangguanchengzaibuzu_rate = [safe_divide(x,y) for x, y in zip(wangguanchengzaibuzu_yixiufu_num,wangguanchengzaibuzu_num )]

print("满意度整治成功率：",manyidu_rate)
print("质差整治成功率：",zhicha_rate)
print("感知类已完成整治成功率：",ganzhilei_rate)

rank_ganzhilei_rate = calculate_rank(ganzhilei_rate[:-2])
print("感知类排名：",rank_ganzhilei_rate)

print("网关承载不足派单",wangguanchengzaibuzu_num)
print("已修复：",wangguanchengzaibuzu_yixiufu_num)
print("网关承载不足转换率：",wangguanchengzaibuzu_rate)

rank_wangguanchengzaibuzu_rate = calculate_rank(wangguanchengzaibuzu_rate[:-2])
print("网关承载不足排名：",rank_wangguanchengzaibuzu_rate)

print("整治成功率排名前三与后三：",zhichaqiansanyuhousan(rank_ganzhilei_rate))
zhichaqiansanhousan = zhichaqiansanyuhousan(rank_ganzhilei_rate)


print("网关承载不足排名前三与后三：",zhichaqiansanyuhousan(rank_wangguanchengzaibuzu_rate))
wangguanqiansanhousan = zhichaqiansanyuhousan(rank_wangguanchengzaibuzu_rate)

print("日期：",today_words)
print("总派单数：",paidan_num[11])
print(f"{ganzhilei_rate[11]:.2%}")
rate_zong = f"{ganzhilei_rate[11]:.2%}"
print(f"{wangguanchengzaibuzu_rate[11]:.2%}")
rate_wangguan = f"{wangguanchengzaibuzu_rate[11]:.2%}"



print('--------------------------------数据情况-----------------------------------------')



print()
print('--------------------------------写入数据-----------------------------------------')
# 写入数据
for index, value in enumerate(paidan_num, start=3):  # 从第3行开始
    des_sheet1.cell(row=index, column=3, value=value)   # 往第3列粘贴数据

for index, value in enumerate(zaituweichuli_num, start=3):  # 从第3行开始
    des_sheet1.cell(row=index, column=4, value=value)   # 往第4列粘贴数据

for index, value in enumerate(manyidu_rate, start=3):  # 从第3行开始
    des_sheet1.cell(row=index, column=5, value=value)   # 往第4列粘贴数据

for index, value in enumerate(zhicha_rate, start=3):  # 从第3行开始
    des_sheet1.cell(row=index, column=6, value=value)   # 往第4列粘贴数据

for index, value in enumerate(ganzhilei_rate, start=3):  # 从第3行开始
    des_sheet1.cell(row=index, column=7, value=value)   # 往第4列粘贴数据

for index, value in enumerate(rank_ganzhilei_rate, start=3):  # 从第3行开始
    des_sheet1.cell(row=index, column=8, value=value)   # 往第4列粘贴数据

for index, value in enumerate(wangguanchengzaibuzu_num, start=3):  # 从第3行开始
    des_sheet1.cell(row=index, column=9, value=value)   # 往第4列粘贴数据

for index, value in enumerate(wangguanchengzaibuzu_yixiufu_num, start=3):  # 从第3行开始
    des_sheet1.cell(row=index, column=10, value=value)   # 往第4列粘贴数据

for index, value in enumerate(wangguanchengzaibuzu_rate, start=3):  # 从第3行开始
    des_sheet1.cell(row=index, column=11, value=value)   # 往第4列粘贴数据

for index, value in enumerate(rank_wangguanchengzaibuzu_rate, start=3):  # 从第3行开始
    des_sheet1.cell(row=index, column=12, value=value)   # 往第4列粘贴数据
print('--------------------------------分表【派单完成情况】已生成-----------------------------------------')

# -----------------------------------------【1.满意度+质差清单】粘贴--------------------------------------
des_sheet2_max_row = des_sheet2.max_row
des_sheet2_max_col = 12    # 由于读取的列数较大，因此手动设置为12

# 数据全部先置空
for row_idx in range(2, des_sheet2_max_row + 1):    # 从第2行开始
    for col_idx in range(2, des_sheet2_max_col + 1):  # 从第2列(B列)开始
        des_sheet2.cell(row=row_idx, column=col_idx).value = None

for idx,row in enumerate(src_sheet.iter_rows(min_row=2)):
    des_sheet2.cell(row=idx+2, column=2).value = row[29].value
    des_sheet2.cell(row=idx+2, column=3).value = row[2].value
    if row[38].value == "":
        des_sheet2.cell(row=idx + 2, column=4).value = Company_name(row[48].value)
        if "十堰控单策略岗位" in row[48].value:
            if row[39].value=="杨宜辉":
                des_sheet2.cell(row=idx + 2, column=4).value = "五堰营维中心"
            if row[39].value=="刘国志" or row[39].value == "高思成":
                des_sheet2.cell(row=idx + 2, column=4).value = "茅箭营维中心"
            if row[39].value=="张勇":
                des_sheet2.cell(row=idx + 2, column=4).value = "张湾营维中心"
    if row[38].value != "":
        des_sheet2.cell(row=idx + 2, column=5).value = Company_name(row[48].value)
        if "十堰控单策略岗位" in row[48].value:
            if row[39].value=="杨宜辉":
                des_sheet2.cell(row=idx + 2, column=4).value = "五堰营维中心"
            if row[39].value=="刘国志" or row[39].value == "高思成":
                des_sheet2.cell(row=idx + 2, column=4).value = "茅箭营维中心"
            if row[39].value=="张勇":
                des_sheet2.cell(row=idx + 2, column=4).value = "张湾营维中心"

    des_sheet2.cell(row=idx+2, column=6).value = row[24].value  # 派单场景
    des_sheet2.cell(row=idx + 2, column=7).value = row[39].value    # 维修人
    des_sheet2.cell(row=idx + 2, column=8).value = Company_name(row[48].value)  # 单位
    if "十堰控单策略岗位" in row[48].value:
        if row[39].value == "杨宜辉":
            des_sheet2.cell(row=idx + 2, column=8).value = "五堰营维中心"
        if row[39].value == "刘国志" or row[39].value == "高思成":
            des_sheet2.cell(row=idx + 2, column=8).value = "茅箭营维中心"
        if row[39].value == "张勇":
            des_sheet2.cell(row=idx + 2, column=8).value = "张湾营维中心"
    des_sheet2.cell(row=idx + 2, column=9).value = row[48].value    # 分局
    # 联系电话
    # 宽带号
    des_sheet2.cell(row=idx + 2, column=10).value = shifouluyin(row[41].value)

last_data_row_sheet2 = 2 + paidan_num[11] - 1
if des_sheet2.max_row > last_data_row_sheet2:
    des_sheet2.delete_rows(last_data_row_sheet2 + 1, des_sheet2.max_row - last_data_row_sheet2)

print('--------------------------------分表【1.满意度+质差清单】已生成-----------------------------------------')
# -----------------------------------------【1.满意度+质差清单】粘贴完成--------------------------------------


# -----------------------------------------【2.网关承载不足清单】粘贴--------------------------------------
des_sheet3_max_row = des_sheet3.max_row
des_sheet3_max_col = 12    # 由于读取的列数较大，因此手动设置为12

# 数据全部先置空
for row_idx in range(2, des_sheet3_max_row + 1):    # 从第2行开始
    for col_idx in range(2, des_sheet3_max_col + 1):  # 从第2列(B列)开始
        des_sheet3.cell(row=row_idx, column=col_idx).value = None

i = 0
for row in src_sheet.iter_rows(min_row=2):
    if row[24].value == "网关承载不足派单":
        des_sheet3.cell(row=i+2, column=2).value = row[29].value
        des_sheet3.cell(row=i+2, column=3).value = row[2].value
        if row[38].value == "":
            des_sheet3.cell(row=i + 2, column=4).value = Company_name(row[48].value)
        if row[38].value != "":
            des_sheet3.cell(row=i + 2, column=5).value = Company_name(row[48].value)
        des_sheet3.cell(row=i+2, column=6).value = row[24].value
        des_sheet3.cell(row=i + 2, column=7).value = row[39].value
        des_sheet3.cell(row=i + 2, column=8).value = Company_name(row[48].value)    # 单位
        des_sheet3.cell(row=i + 2, column=9).value = row[48].value      # 分局
        # 联系电话
        # 宽带号
        des_sheet3.cell(row=i + 2, column=10).value = shifouluyin(row[41].value)
        des_sheet3.cell(row=i + 2, column=11).value = row[36].value
        des_sheet3.cell(row=i + 2, column=12).value = row[49].value

        i += 1

last_data_row_sheet3 = 2 + wangguanchengzaibuzu_num[11] - 1
if des_sheet3.max_row > last_data_row_sheet3:
    des_sheet3.delete_rows(last_data_row_sheet3 + 1, des_sheet3.max_row - last_data_row_sheet3)

print('--------------------------------分表【2.网关承载不足清单】已生成-----------------------------------------')
# -----------------------------------------【2.网关承载不足清单】粘贴完成--------------------------------------





# 数据全部先置空
for row_idx in range(2, 200):    # 从第2行开始
    for col_idx in range(1, 6):  # 从第1列(B列)开始
        des_sheet4.cell(row=row_idx, column=col_idx).value = None


def sort_with_indices(lst):
    # 组合索引与值，并按值降序排序（相同值保持原顺序）
    sorted_pairs = sorted(enumerate(lst), key=lambda x: (x[1], x[0]))
    # 提取排序后的值和索引
    sorted_values = [x[1] for x in sorted_pairs]
    sorted_indices = [x[0] for x in sorted_pairs]
    return sorted_values, sorted_indices


# 此处是统计各分公司的已修复率，汇总到14、15、16、17列，从第3行开始，需要的话把注释开启即可
lst = []
lst2 = []
lst3 = []
lst4 = []
lst5 = []
lst6 = []
lst7 = []

for idx,row in enumerate(src_sheet.iter_rows(min_row=2)):
    if row[48].value not in lst:
        if "十堰控单策略岗位" not in row[48].value:
            lst.append(row[48].value)
        if "十堰控单策略岗位" in row[48].value:
            if row[39].value=="杨宜辉":
                if "十堰控单策略岗位-五堰（控单分不清的五个单位）" not in lst:
                    lst.append("十堰控单策略岗位-五堰（控单分不清的五个单位）")
            if row[39].value=="刘国志" or row[39].value == "高思成":
                if "十堰控单策略岗位-茅箭（控单分不清的五个单位）" not in lst:
                    lst.append("十堰控单策略岗位-茅箭（控单分不清的五个单位）")
            if row[39].value=="张勇":
                if "十堰控单策略岗位-张湾（控单分不清的五个单位）" not in lst:
                    lst.append("十堰控单策略岗位-张湾（控单分不清的五个单位）")

num_fenju = len(lst)

for i in range(num_fenju):
    lst2.append(0)
    lst3.append(0)
    lst5.append(0)
    lst6.append(0)

for i in range(num_fenju):
    lst4.append("")
    lst7.append("")
print("============================================================================================")

print(lst)
print(len(lst))


for row in src_sheet.iter_rows(min_row=2):
    for i in range(len(lst)):
        if row[48].value == lst[i]:
            lst2[i] += 1
            if row[2].value == "已修复":
                lst3[i] += 1
        if row[48].value == "十堰控单策略岗位（控单分不清的五个单位）" and row[39].value == "杨宜辉":
            if lst[i] == "十堰控单策略岗位-五堰（控单分不清的五个单位）":
                lst2[i] += 1
                if row[2].value == "已修复":
                    lst3[i] += 1
        if row[48].value == "十堰控单策略岗位（控单分不清的五个单位）" and (row[39].value == "刘国志" or row[39].value == "高思成"):
            if lst[i] == "十堰控单策略岗位-茅箭（控单分不清的五个单位）":
                lst2[i] += 1
                if row[2].value == "已修复":
                    lst3[i] += 1
        if row[48].value == "十堰控单策略岗位（控单分不清的五个单位）" and row[39].value == "张勇":
            if lst[i] == "十堰控单策略岗位-张湾（控单分不清的五个单位）":
                lst2[i] += 1
                if row[2].value == "已修复":
                    lst3[i] += 1


print(lst2)
print(len(lst2))
print("============================================================================================")

zerenbumenxiufu_rate = [safe_divide(x,y) for x, y in zip(lst3,lst2 )]
zerenbumenxiufu_rate_paiming,zerenbumenxiufu_rate_paiming_index = sort_with_indices(zerenbumenxiufu_rate)


def fengongsi_name(fenju = ""):
    str = ""
    if "房县" in fenju:
        str = "房县"
    if "丹江口" in fenju:
        str = "丹江口"
    if "郧阳区" in fenju:
        str = "郧阳区"
    if "郧西" in fenju:
        str = "郧西县"
    if "竹山" in fenju:
        str = "竹山县"
    if "竹溪" in fenju:
        str = "竹溪县"
    if "五堰" in fenju:
        str = "五堰"
    if "茅箭" in fenju:
        str = "茅箭"
    if "张湾" in fenju:
        str = "张湾"
    if "武当山" in fenju:
        str = "武当山"
    if "十堰现场综合化" in fenju:
        str = "线路中心"
    if "十堰控单策略岗位" in fenju:
        if "五堰" in fenju:
            str = "五堰"
        if "茅箭" in fenju:
            str = "茅箭"
        if "张湾" in fenju:
            str = "张湾"

    return str

n = 0
for i in zerenbumenxiufu_rate_paiming_index:
    lst7[n] = fengongsi_name(lst[i])
    lst4[n] = lst[i]
    lst5[n] = lst2[i]
    lst6[n] = lst3[i]
    n += 1

fengongsi = ["丹江口","房县","郧西县","郧阳区","竹山县","竹溪县","五堰","茅箭","张湾","武当山","线路中心"]
fengongsi_xiufupaimingjiaodidanwei = [0,0,0,0,0,0,0,0,0,0,0]

m = 0
for i in zerenbumenxiufu_rate_paiming:
    if i <= 0.8:
        if lst7[m] == "丹江口":
            fengongsi_xiufupaimingjiaodidanwei[0]+=1
        if lst7[m] == "房县":
            fengongsi_xiufupaimingjiaodidanwei[1]+=1
        if lst7[m] == "郧西县":
            fengongsi_xiufupaimingjiaodidanwei[2]+=1
        if lst7[m] == "郧阳区":
            fengongsi_xiufupaimingjiaodidanwei[3]+=1
        if lst7[m] == "竹山县":
            fengongsi_xiufupaimingjiaodidanwei[4]+=1
        if lst7[m] == "竹溪县":
            fengongsi_xiufupaimingjiaodidanwei[5]+=1
        if lst7[m] == "五堰":
            fengongsi_xiufupaimingjiaodidanwei[6]+=1
        if lst7[m] == "茅箭":
            fengongsi_xiufupaimingjiaodidanwei[7]+=1
        if lst7[m] == "张湾":
            fengongsi_xiufupaimingjiaodidanwei[8]+=1
        if lst7[m] == "武当山":
            fengongsi_xiufupaimingjiaodidanwei[9]+=1
        if lst7[m] == "线路中心":
            fengongsi_xiufupaimingjiaodidanwei[10]+=1
    m+=1


row = 2
for idx,company_num in enumerate(fengongsi_xiufupaimingjiaodidanwei):
    des_sheet4.cell(row=row, column=7, value=fengongsi[idx])
    des_sheet4.cell(row=row, column=8, value=company_num)
    row += 1

print("-----------------------------排序情况----------------------------------")
print(lst5)
print(lst6)


# for index, value in enumerate(lst, start=3):  # 从第3行开始
#     des_sheet4.cell(row=index, column=1, value=value)   # 往第4列粘贴数据
# for index, value in enumerate(lst2, start=3):  # 从第3行开始
#     des_sheet4.cell(row=index, column=2, value=value)   # 往第4列粘贴数据
# for index, value in enumerate(lst3, start=3):  # 从第3行开始
#     des_sheet4.cell(row=index, column=3, value=value)   # 往第4列粘贴数据
# for index, value in enumerate(zerenbumenxiufu_rate, start=3):  # 从第3行开始
#     des_sheet4.cell(row=index, column=4, value=value)   # 往第4列粘贴数据

for index, value in enumerate(lst7, start=2):  # 从第3行开始
    des_sheet4.cell(row=index, column=1, value=value)   # 往第4列粘贴数据
for index, value in enumerate(lst4, start=2):  # 从第3行开始
    des_sheet4.cell(row=index, column=2, value=value)   # 往第4列粘贴数据
for index, value in enumerate(lst5, start=2):  # 从第3行开始
    des_sheet4.cell(row=index, column=3, value=value)   # 往第4列粘贴数据
for index, value in enumerate(lst6, start=2):  # 从第3行开始
    des_sheet4.cell(row=index, column=4, value=value)   # 往第4列粘贴数据
for index, value in enumerate(zerenbumenxiufu_rate_paiming, start=2):  # 从第3行开始
    des_sheet4.cell(row=index, column=5, value=value)   # 往第4列粘贴数据

print()
# 保存文件
des_wb.save(des_sheet_file_path)
des_wb.close()
print('--------------------------------文件已保存-----------------------------------------')

# out_words = "@所有人 各位好 ：截止到{date}：派单{paidan_num}张，分公司整治成功率{rate_zong}，其中{zong1}、{zong2}、{zong3}排名靠前，{zong10}、{zong9}、{zong8}排名靠后，网关承载不足转化率{rate_wangguan}，其中{wangguan1}、{wangguan2}、{wangguan3}排名靠前，{wangguan10}、{wangguan9}、{wangguan8}排名靠后。请各分公司进行高效整治".format(date =today_words,paidan_num = paidan_num[11],rate_zong=rate_zong,zong1=zhichaqiansanhousan[0],zong2=zhichaqiansanhousan[1],zong3=zhichaqiansanhousan[2],zong10=zhichaqiansanhousan[3],zong9=zhichaqiansanhousan[4],zong8=zhichaqiansanhousan[5],rate_wangguan=rate_wangguan,wangguan1=wangguanqiansanhousan[0],wangguan2=wangguanqiansanhousan[1],wangguan3=wangguanqiansanhousan[2],wangguan10=wangguanqiansanhousan[3],wangguan9=wangguanqiansanhousan[4],wangguan8=wangguanqiansanhousan[5])
out_words = "@所有人 各位好 ：截止到{date}：派单{paidan_num}张，分公司整治成功率{rate_zong}，其中{zong1}、{zong2}、{zong3}排名靠前，{zong10}、{zong9}、{zong8}排名靠后，网关承载不足转化率暂时未派单。请各分公司进行高效整治".format(date =today_words,paidan_num = paidan_num[11],rate_zong=rate_zong,zong1=zhichaqiansanhousan[0],zong2=zhichaqiansanhousan[1],zong3=zhichaqiansanhousan[2],zong10=zhichaqiansanhousan[3],zong9=zhichaqiansanhousan[4],zong8=zhichaqiansanhousan[5])

print(out_words)


